﻿using System;
using System.Data;
using System.Data.SqlClient;
using CounterServerModel;

namespace SmartCountersDeployer
{
    /// <summary>
    /// A class that deploys the DB.
    /// </summary>
    public class DBDeployer
    {
		private string m_SQLServerAddress;
		private string m_DBName;
        private string m_SQLAdminUserName;
        private string m_SQLAdminPassword;
		private string m_AppUserName;
		private string m_AppPassword;

        private string m_MasterConnectionString;
        private string m_DBConnectionString;

		private SqlConnection m_MasterSqlConnection;
		private SqlConnection m_DBSqlConnection;
        private SqlCommand m_MasterSqlCommand;
		private SqlCommand m_DBSqlCommand;

        public DBDeployer(string i_SQLAdminUserName, string i_SQLAdminPassword, string i_SQLServerAddress, string i_DBName, string i_AppUserName, string i_AppPassword)
        {
			m_SQLServerAddress = i_SQLServerAddress;
			m_DBName = i_DBName;
			m_SQLAdminUserName = i_SQLAdminUserName;
			m_SQLAdminPassword = i_SQLAdminPassword;
			m_AppUserName = i_AppUserName;
			m_AppPassword = i_AppPassword;

            m_MasterConnectionString = string.Format("Data Source={0};Initial Catalog=master;User Id={1};Password={2};Trusted_Connection=True;", m_SQLServerAddress, m_SQLAdminUserName, m_SQLAdminPassword);

			m_DBConnectionString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};", m_SQLServerAddress, m_DBName, i_AppUserName, i_AppPassword);

            m_MasterSqlConnection = new SqlConnection(m_MasterConnectionString);
			m_MasterSqlCommand = new SqlCommand();
			m_MasterSqlCommand.Connection = m_MasterSqlConnection;

			m_DBSqlConnection = new SqlConnection(m_DBConnectionString);
			m_DBSqlCommand = new SqlCommand();
			m_DBSqlCommand.Connection = m_DBSqlConnection;

			m_MasterSqlCommand.Parameters.AddWithValue("@DBName", m_DBName);
			m_MasterSqlCommand.Parameters.AddWithValue("@UserName", m_AppUserName);
			m_MasterSqlCommand.Parameters.AddWithValue("@UserPassword", m_AppPassword);
        }

        public void DeployDB() 
        {
			// Create Application DB and User
			m_MasterSqlConnection.Open();
			CreateDB();
			CreateDBAppUser();
			m_MasterSqlConnection.Close();

			// Initilize Application Data
			m_DBSqlConnection.Open();
			CreateTables();
			InsertAdminUser();
			m_DBSqlConnection.Close();

			Configurator configurator = new Configurator();
			configurator.Configure(m_SQLServerAddress, m_DBName, m_AppUserName, m_AppPassword);
        }

        private void CreateDB()
        {

            m_MasterSqlCommand.CommandText = string.Format("CREATE DATABASE [{0}] COLLATE Hebrew_CI_AI;", m_MasterSqlCommand.Parameters["@DBName"].Value);
			m_MasterSqlCommand.ExecuteNonQuery();
        }

		private void CreateDBAppUser()
		{
			m_MasterSqlCommand.CommandText = string.Format(@"CREATE LOGIN [{0}] 
														WITH PASSWORD=N'{1}', 
														DEFAULT_DATABASE=[{2}], 
														DEFAULT_LANGUAGE=[us_english], 
														CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
														
														ALTER LOGIN [{0}] ENABLE
														
														USE [{2}]
														CREATE USER [{0}] FOR LOGIN [{0}];
														EXEC sp_addrolemember 'db_owner', '{0}'",
														m_MasterSqlCommand.Parameters["@UserName"].Value,
														m_MasterSqlCommand.Parameters["@UserPassword"].Value,
														m_MasterSqlCommand.Parameters["@DBName"].Value);

			m_MasterSqlCommand.ExecuteNonQuery();
		}

		private void InsertAdminUser()
        {
            m_DBSqlCommand.CommandText = @"INSERT INTO Users (Username, Permissions, Phone, MobilePhone, Email, Password) VALUES ('admin', '2097151', '0', '0', 'admin@smartcounters.org', '12345678');";

			m_DBSqlCommand.ExecuteNonQuery();
        }

        private void CreateTables()
        {
			m_DBSqlCommand.CommandText = @"CREATE TABLE Users(
                                         [UserID] [int] IDENTITY(1,1) NOT NULL,
	                                     [Username] [nvarchar](50) NOT NULL,
	                                     [Permissions] [int] NOT NULL,
	                                     [Phone] [nvarchar](50) NOT NULL,
	                                     [MobilePhone] [nvarchar](50) NOT NULL,
	                                     [Email] [nvarchar](100) NOT NULL,
	                                     [Password] [nvarchar](50) NOT NULL,
                                         PRIMARY KEY (UserID));";

			m_DBSqlCommand.ExecuteNonQuery();

			m_DBSqlCommand.CommandText = @"CREATE TABLE Accounts(
	                                     [AccountID] [int] IDENTITY(1,1) NOT NULL,
	                                     [Country] [nvarchar](50) NOT NULL,
	                                     [City] [nvarchar](50) NOT NULL,
	                                     [Street] [nvarchar](50) NOT NULL,
	                                     [House] [nvarchar](50) NOT NULL,
	                                     [Apartment] [int] NULL,
	                                     [Phone] [nvarchar](50) NOT NULL,
                                         [Longitude] [decimal](8, 6) NOT NULL,
                                         [Latitude] [decimal](8, 6) NOT NULL,
                                         PRIMARY KEY (AccountID));";

			m_DBSqlCommand.ExecuteNonQuery();

			m_DBSqlCommand.CommandText = @"CREATE TABLE [Accounts-Users](
	                                     [AccountID] [int] NOT NULL,
	                                     [UserID] [int] NOT NULL,
                                         PRIMARY KEY (AccountID, UserID),
                                         CONSTRAINT AccountFK1 FOREIGN KEY (AccountID)
                                            REFERENCES Accounts(AccountID)
                                            ON DELETE CASCADE
                                            ON UPDATE CASCADE,
                                         CONSTRAINT UserFK1 FOREIGN KEY (UserID)
                                            REFERENCES Users(UserID)
                                            ON DELETE CASCADE
                                            ON UPDATE CASCADE);";

			m_DBSqlCommand.ExecuteNonQuery();

			m_DBSqlCommand.CommandText = @"CREATE TABLE Usages(
	                                     [AccountID] [int] NOT NULL,
	                                     [UpdateDate] [datetime] NOT NULL,
	                                     [TotalUsage] [decimal](18, 2) NOT NULL,
                                         PRIMARY KEY (AccountID, UpdateDate),
                                         CONSTRAINT AccountFK2 FOREIGN KEY (AccountID)
                                            REFERENCES Accounts(AccountID)
                                            ON DELETE CASCADE
                                            ON UPDATE CASCADE);";

			m_DBSqlCommand.ExecuteNonQuery();

			m_DBSqlCommand.CommandText = @"CREATE TABLE Faults(
	                                     [FaultID] [int] IDENTITY(1,1) NOT NULL,
	                                     [UserID] [int] NOT NULL,
                                         [AccountID] [int] NOT NULL,
	                                     [FaultDate] [datetime] NOT NULL,
                                         [Description] [text] NOT NULL,
                                         [IsSolved] [bit] NOT NULL,
                                         PRIMARY KEY (FaultID),
                                         CONSTRAINT UserFK2 FOREIGN KEY (UserID)
                                            REFERENCES Users(UserID)
                                            ON DELETE CASCADE
                                            ON UPDATE CASCADE,
                                         CONSTRAINT AccountFK3 FOREIGN KEY (AccountID)
                                            REFERENCES Accounts(AccountID)
                                            ON DELETE CASCADE
                                            ON UPDATE CASCADE);";

			m_DBSqlCommand.ExecuteNonQuery();

			m_DBSqlCommand.CommandText = @"CREATE TABLE Bills(
	                                     [PaymentYear] [int] NOT NULL,
	                                     [PaymentMonth] [int] NOT NULL,
	                                     [AccountID] [int] NOT NULL,
                                         [TotalUsage] [decimal](18, 2) NOT NULL,
                                         [SumPaid] [decimal](18, 2) NOT NULL,
                                         PRIMARY KEY (PaymentYear, PaymentMonth, AccountID),
                                         CONSTRAINT AccountFK4 FOREIGN KEY (AccountID)
                                            REFERENCES Accounts(AccountID)
                                            ON DELETE CASCADE
                                            ON UPDATE CASCADE);";

			m_DBSqlCommand.ExecuteNonQuery();

			m_DBSqlCommand.CommandText = @"CREATE TABLE BillingProperties(
	                                     [StartDate] [datetime] NOT NULL,
	                                     [EndDate] [datetime] NULL,
	                                     [PriceForLiter] [decimal](18, 2) NOT NULL,
                                         [Comments] [text] NOT NULL);";

			m_DBSqlCommand.ExecuteNonQuery();
        }

        public void InsertDummyInformation()
        {
            m_DBSqlConnection.Open();
            
            // Insert Dummy Users:
            m_DBSqlCommand.CommandText = "INSERT INTO Users (Username, Permissions, Phone, MobilePhone, Email, Password) VALUES ('eugene', '1840458', '11111111', '11111111', 'eugene@smartcounters.org', 'eugene');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Users (Username, Permissions, Phone, MobilePhone, Email, Password) VALUES ('gabi', '1840458', '22222222', '22222222', 'gabi@smartcounters.org', 'gabi');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Users (Username, Permissions, Phone, MobilePhone, Email, Password) VALUES ('arthur', '1840458', '33333333', '33333333', 'arthur@smartcounters.org', 'arthur');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Users (Username, Permissions, Phone, MobilePhone, Email, Password) VALUES ('kobi', '1840458', '44444444', '44444444', 'kobi@smartcounters.org', 'kobi');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Users (Username, Permissions, Phone, MobilePhone, Email, Password) VALUES ('john', '1840458', '55555555', '55555555', 'john@smartcounters.org', 'john');";
            m_DBSqlCommand.ExecuteNonQuery();

            // Insert Dummy Accounts:
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'Ashdod', 'Avot-Eshurun', '12', '12', '11111111', '34.650337', '31.794444');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'Holon', 'Golomb', '52', '1', '22222222', '34.774266', '32.016091');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'Holon', 'Golomb', '52', '2', '44444444', '34.774266', '32.016091');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'Ramla', 'Geva', '5', '12', '089557548', '34.875787', '31.924703');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'Herzliya', 'Ben Gurion', '17', '20', '039887544', '34.842689', '32.163979');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'jerusalem', 'harav kuk', '45', '9', '025448754', '35.220612', '31.783569');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'Kiryat Gat', 'Glikson', '41', '9', '082248754', '34.770452', '31.614276');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'Netanya', 'HaMelacha', '63', '1', '025499954', '34.860735', '32.285219');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'Beer Sheva', 'Gideon Hausner', '88', '9', '085669851', '34.805256', '31.287381');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'Haifa', 'Finland', '54', '3', '045669851', '34.993064', '32.767736');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO Accounts (Country, City, Street, House, Apartment, Phone, Longitude, Latitude) VALUES ('Israel', 'Askelon', 'Ben David', '70', '2', '085669851', '34.590669', '31.668203');";
            m_DBSqlCommand.ExecuteNonQuery();

            // Insert Dummy Associations:
            m_DBSqlCommand.CommandText = "INSERT INTO [Accounts-Users] (AccountID, UserID) VALUES ('1', '2');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO [Accounts-Users] (AccountID, UserID) VALUES ('1', '6');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO [Accounts-Users] (AccountID, UserID) VALUES ('2', '3');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO [Accounts-Users] (AccountID, UserID) VALUES ('3', '4');";
            m_DBSqlCommand.ExecuteNonQuery();
            m_DBSqlCommand.CommandText = "INSERT INTO [Accounts-Users] (AccountID, UserID) VALUES ('3', '5');";
            m_DBSqlCommand.ExecuteNonQuery();

            // Insert Billing Properties (Price):
            DateTime date = new DateTime(2012, 05, 07);
            m_DBSqlCommand.CommandText = string.Format(
                "INSERT INTO BillingProperties (StartDate, PriceForLiter, Comments) VALUES ('{0}', '0.01', 'Initial Price.');",
				date.ToString("yyyy/MM/dd hh:mm:ss:mmm"));
            m_DBSqlCommand.ExecuteNonQuery();

            // Insert Dummy Usage for the 3 Accounts.
            DateTime updateDate = new DateTime(2012, 06, 01, 01, 01, 01);
            double usage1 = 0, usage2 = 0, usage3 = 0;

            for (int i = 0; i < 44640; i++)
            {
                if (i < 14880)
                {
                    usage1 += (15000 - i) / 10000;
                }
                else if (i < 29760)
                {
                    usage1 += 10 - (i / 100000);
                }
                else
                {
                    usage1 += i / 100000000;
                }

				m_DBSqlCommand.CommandText = string.Format("INSERT INTO [Usages] (AccountID, UpdateDate, TotalUsage) VALUES ('1', '{0}', '{1}');", updateDate.ToString("yyyy/MM/dd HH:mm:ss:mmm"), usage1);
                m_DBSqlCommand.ExecuteNonQuery();

                usage2 += i / 10000;

				m_DBSqlCommand.CommandText = string.Format("INSERT INTO [Usages] (AccountID, UpdateDate, TotalUsage) VALUES ('2', '{0}', '{1}');", updateDate.ToString("yyyy/MM/dd HH:mm:ss:mmm"), usage2);
                m_DBSqlCommand.ExecuteNonQuery();

                usage3 += i % 2 == 0 ? 0.5 : 0;

				m_DBSqlCommand.CommandText = string.Format("INSERT INTO [Usages] (AccountID, UpdateDate, TotalUsage) VALUES ('3', '{0}', '{1}');", updateDate.ToString("yyyy/MM/dd HH:mm:ss:mmm"), usage3);
                m_DBSqlCommand.ExecuteNonQuery();

                updateDate = updateDate.AddMinutes(3);
            }

            // Insert Dummy Faults:
            DateTime faultDate = new DateTime(2012, 01, 07, 15, 20, 52);
			m_DBSqlCommand.CommandText = string.Format("INSERT INTO Faults (AccountID,UserID, FaultDate, Description, IsSolved) VALUES ('1','2', '{0}', 'No water.', 'False');", faultDate.ToString("yyyy/MM/dd hh:mm:ss:mmm"));
            m_DBSqlCommand.ExecuteNonQuery();

            faultDate = faultDate.AddDays(1).AddHours(2).AddMinutes(8).AddSeconds(1);

			m_DBSqlCommand.CommandText = string.Format("INSERT INTO Faults (AccountID,UserID, FaultDate, Description, IsSolved) VALUES ('2','2', '{0}', 'No water.', 'False');", faultDate.ToString("yyyy/MM/dd hh:mm:ss:mmm"));
            m_DBSqlCommand.ExecuteNonQuery();
            
            m_DBSqlConnection.Close();
        }
    }
}
